package com.shooter.dao;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.shooter.utils.Check;
import com.shooter.utils.PrintUtil;

import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class DBManager {
    public static DataSource dataSource = null;

    static {
        ComboPooledDataSource pool = new ComboPooledDataSource();
        try {
            pool.setDriverClass(DbConfig.driver_class);
            pool.setJdbcUrl(DbConfig.jdbc_url);
            pool.setUser(DbConfig.db_username);
            pool.setPassword(DbConfig.db_password);
            pool.setMaxPoolSize(DbConfig.maxPoolSize);
            pool.setMinPoolSize(DbConfig.minPoolSize);
            pool.setMaxIdleTime(DbConfig.maxIdleTime);
            pool.setIdleConnectionTestPeriod(DbConfig.idleConnectionTestPeriod);
            pool.setCheckoutTimeout(DbConfig.checkoutTimeout);
            pool.setAcquireIncrement(DbConfig.acquireIncrement);
            pool.setMaxStatements(DbConfig.maxStatements);
            dataSource = pool;
        } catch (Exception e) {
            PrintUtil.printError(e);
            PrintUtil.printMsg("数据连接池加载失败");
        }
    }

    public static DBManager dbManager = null;

    public static DBManager getDbManager() {
        if (dbManager == null) {
            dbManager = new DBManager();
        }
        return dbManager;
    }

    private Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    /**
     * 插入操作（单数操作）
     *
     * @param dataDb
     * @return
     */
    public static boolean insert(BaseDataDb dataDb) {
        ArrayList<String> arrayList = new ArrayList<>();
        arrayList.add(dataDb.toInsertSql());
        return transactionCommit(arrayList);
    }

    /**
     * 插入操作（批量操作）
     *
     * @param dataDbs
     * @return
     */
    public static boolean insertAll(ArrayList<BaseDataDb> dataDbs) {
        ArrayList<String> arrayList = new ArrayList<>();
        for (int i = 0; i < dataDbs.size(); i++) {
            arrayList.add(dataDbs.get(i).toInsertSql());
        }
        return transactionCommit(arrayList);
    }

    /**
     * 更新操作（单数操作）
     *
     * @param dataDb
     * @param where  默认不填 则自动根据id去判定
     * @return
     */
    public static boolean update(BaseDataDb dataDb, SQLWhere where) {
        ArrayList<String> arrayList = new ArrayList<>();
        arrayList.add(dataDb.toUpdateSql(where));
        return transactionCommit(arrayList);
    }

    /**
     * 更新操作（批量操作）
     *
     * @param dataDbs
     * @return
     */
    public static boolean updateAll(ArrayList<BaseDataDb> dataDbs) {
        ArrayList<String> arrayList = new ArrayList<>();
        for (int i = 0; i < dataDbs.size(); i++) {
            arrayList.add(dataDbs.get(i).toUpdateSql());
        }
        return transactionCommit(arrayList);
    }

    /**
     * 删除操作（单个操作）
     *
     * @param dataDb
     * @param where  默认不填 则自动根据id去判定
     * @return
     */
    public static boolean delete(BaseDataDb dataDb, SQLWhere where) {
        ArrayList<String> arrayList = new ArrayList<>();
        arrayList.add(dataDb.toDeleteSql(where));
        return transactionCommit(arrayList);
    }

    /**
     * 删除操作（批量操作） 自动根据id去判定
     *
     * @param dataDbs
     * @return
     */
    public static boolean deleteAll(ArrayList<BaseDataDb> dataDbs) {
        ArrayList<String> arrayList = new ArrayList<>();
        for (int i = 0; i < dataDbs.size(); i++) {
            arrayList.add(dataDbs.get(i).toDeleteSql());
        }
        return transactionCommit(arrayList);
    }


    /**
     * 查询单个对象（单数操作）
     *
     * @param mClass 查询的数据对象类型
     * @param where  默认不填 则自动根据已有数值判定
     * @return
     */
    public static Object querySingle(Class mClass, SQLWhere where) {
        Object obj = null;
        Field[] fields = Check.getField(mClass);
        String tab = mClass.getSimpleName();
        String sql = "SELECT * FROM " + tab;
        Connection dbConn = null;
        ResultSet set = null;
        PreparedStatement statement = null;
        try {
            String tempWhere = "";
            if (where != null) {
                tempWhere += where.getWhere();
                tempWhere += where.getGrouo();
                tempWhere += where.getOrder();
            }
            sql += tempWhere;
            dbConn = getDbManager().getConnection();
            statement = dbConn.prepareStatement(sql);
            set = statement.executeQuery();
            if (set.next()) {
                obj = mClass.newInstance();
                for (int i = 0; i < fields.length; i++) {
                    fields[i].set(obj, set.getString(fields[i].getName()));
                }
            }
        } catch (IllegalAccessException e) {
            PrintUtil.printError(e);
        } catch (SQLException e) {
            PrintUtil.printError(e);
        } catch (InstantiationException e) {
            PrintUtil.printError(e);
        } finally {
            closeConnect(dbConn, statement, set);
        }
        return obj;
    }

    /**
     * 查询单个对象（单数操作）
     *
     * @param data 根据数据中已存在的数据进行查询
     * @return
     */
    public static Object querySingle(BaseDataDb data) {
        Object obj = null;
        Field[] fields = Check.getField(data.getClass());
        List<String> queryKey = new ArrayList<>();
        String tab = data.getClass().getSimpleName();
        String sql = "SELECT * FROM " + tab;
        Connection dbConn = null;
        ResultSet set = null;
        PreparedStatement statement = null;
        try {
            String tempWhere = "";
            for (int i = 0; i < fields.length; i++) {
                Field field = fields[i];
                String key = field.getName();
                String value = (String) field.get(data);
                queryKey.add(key);
                if (value != null && value.length() > 0) {
                    if (tempWhere.length() == 0) {
                        tempWhere += " WHERE ";
                    } else {
                        tempWhere += " AND ";
                    }
                    if (isSqlWhere(value)) {
                        tempWhere += key + "=" + value + "";
                    } else {
                        tempWhere += key + "='" + value + "'";
                    }
                }
            }
            sql += tempWhere;
            dbConn = getDbManager().getConnection();
            statement = dbConn.prepareStatement(sql);
            set = statement.executeQuery();
            if (set.next()) {
                obj = data.getClass().newInstance();
                for (int i = 0; i < fields.length; i++) {
                    fields[i].set(obj, set.getString(fields[i].getName()));
                }
            }
        } catch (IllegalAccessException e) {
            PrintUtil.printError(e);
        } catch (SQLException e) {
            PrintUtil.printError(e);
        } catch (InstantiationException e) {
            PrintUtil.printError(e);
        } finally {
            closeConnect(dbConn, statement, set);
        }
        return obj;
    }

    /**
     * 查询数据库（批量查询）
     *
     * @param mClass 查询的数据对象类型
     * @param <T>
     * @param where  默认不填 则自动根据id去判定
     * @return
     */
    public static <T> ArrayList<T> query(Class mClass, SQLWhere where) {
        ArrayList<T> t = new ArrayList<>();
        String tab = mClass.getSimpleName();
        String sql = fromSqlUpdateString(tab, BaseDataDb.getAllKey(mClass), where);
        Connection dbConn = null;
        ResultSet set = null;
        PreparedStatement statement = null;
        try {
            dbConn = getDbManager().getConnection();
            statement = dbConn.prepareStatement(sql);
            set = statement.executeQuery();
            byResultQuery(mClass, set, t);
        } catch (SQLException e) {
            PrintUtil.printError(e);
        } finally {
            closeConnect(dbConn, statement, set);
        }
        return t;
    }

    /**
     * 查询数据条数
     *
     * @param mClass
     * @param where
     * @return
     */
    public static int queryCount(Class mClass, SQLWhere where) {
        int count = 0;
        Connection dbConn = null;
        ResultSet set = null;
        PreparedStatement statement = null;
        try {
            BaseDataDb dataDb = (BaseDataDb) mClass.newInstance();
            String sql = "select count(" + dataDb.getKeyId() + ") from " + mClass.getSimpleName() + (where != null ? where.getWhere() : "");
            dbConn = getDbManager().getConnection();
            statement = dbConn.prepareStatement(sql);
            set = statement.executeQuery();
            if (set.next()) {
                count = set.getInt(1);
            }
        } catch (InstantiationException e) {
            PrintUtil.printError(e);
        } catch (IllegalAccessException e) {
            PrintUtil.printError(e);
        } catch (SQLException e) {
            PrintUtil.printError(e);
        } finally {
            closeConnect(dbConn, statement, set);
        }
        return count;
    }

    private static String toSqlString(Class mClass, String[] keys, String[] values) {
        try {
            if (keys != null || values != null) {
                if (keys.length != values.length) {
                    throw new SQLException("keys 和 values 长度不一致!");
                }
            }
            // 获取数据库连接对象
            String sql = "select * from " + mClass.getSimpleName();
            if (keys != null && keys.length != 0) {
                sql += " where";
                for (int i = 0; i < keys.length; i++) {
                    if (i > 0) {
                        sql += " and";
                    }
                    sql += " " + keys[i] + "=?";
                }
            }
            return sql;
        } catch (SQLException e) {
            PrintUtil.printError(e);
        }
        return null;
    }

    public static <T> Object querySingle(Class mClass, String[] keys, String[] values) {
        ArrayList<T> t = new ArrayList<>();
        Connection dbConn = null;
        ResultSet set = null;
        PreparedStatement statement = null;
        try {
            // 获取数据库连接对象
            String sql = toSqlString(mClass, keys, values);
            if (sql != null) {
                dbConn = getDbManager().getConnection();
                statement = dbConn.prepareStatement(sql);
                if (values != null && values.length != 0) {
                    for (int i = 0; i < values.length; i++) {
                        // 设置占位符的值，第一个参数是占位符的位置，第二个参数是要设置的值
                        statement.setString(i + 1, values[i]);
                    }
                }
                set = statement.executeQuery();
                byResultQuery(mClass, set, t);
                if (t != null && t.size() != 0) {
                    return t.get(0);
                }
            }
        } catch (SQLException e) {
            PrintUtil.printError(e);
        } finally {
            closeConnect(dbConn, statement, set);
        }
        // 执行查询
        return null;
    }

    /**
     * 根据Result查询出来的参数转换集合
     *
     * @param aClass 反射的数据
     * @param set    数据库的数据
     * @param list   返回的数据源
     * @param <T>
     */
    public static <T> void byResultQuery(Class aClass, ResultSet set, ArrayList<T> list) {
        try {
            while (set.next()) {
                T obj = (T) aClass.newInstance();
                Field[] fields = Check.getField(aClass);
                for (int i = 0; i < fields.length; i++) {
                    fields[i].set(obj, set.getString(fields[i].getName()));
                }
                list.add(obj);
            }
        } catch (SQLException e) {
            PrintUtil.printError(e);
        } catch (IllegalAccessException e) {
            PrintUtil.printError(e);
        } catch (InstantiationException e) {
            PrintUtil.printError(e);
        }
    }

    /**
     * 格式化sql Select查询语句
     *
     * @param tab      表明
     * @param queryKey 查询的key
     * @param where    where查询条件
     * @return
     */
    public static String fromSqlUpdateString(String tab, ArrayList<String> queryKey, SQLWhere where) {
        String sql = "";
        if (queryKey != null) {
            sql = "select ";
            for (int i = 0; i < queryKey.size(); i++) {
                if (i > 0) {
                    sql += ",";
                }
                sql += queryKey.get(i);
            }
            sql += " from " + tab;
        } else {
            sql = "select * from " + tab;
        }
        if (where != null) {
            sql += where.getWhere();
            sql += where.getGrouo();
            sql += where.getOrder();
            sql += where.getPageWhere();
        }
        return sql;
    }

    //事物提交
    public static boolean transactionCommit(ArrayList<String> dataDbs) {
        Connection conn = null;
        Statement stmt = null;
        boolean flag = false;
        try {
            conn = getDbManager().getConnection();
            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            for (int i = 0; i < dataDbs.size(); i++) {
                stmt.addBatch(dataDbs.get(i));
            }
            stmt.executeBatch();
            conn.commit();
            flag = true;
        } catch (SQLException e) {
            PrintUtil.printError(e);
            try {
                conn.rollback();
            } catch (SQLException e2) {
                PrintUtil.printError(e2);
            }
        } finally {
            try {
                if (conn != null) conn.close();
            } catch (SQLException e) {
                PrintUtil.printError(e);
            }
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException e) {
                PrintUtil.printError(e);
            }
        }
        return flag;
    }

    /**
     * 关闭链接的数据库
     */
    private static void closeConnect(Connection conn, PreparedStatement ps, ResultSet set) {
        try {
            if (conn != null) conn.close();
        } catch (SQLException e) {
            PrintUtil.printError(e);
        }
        try {
            if (ps != null) ps.close();
        } catch (SQLException e) {
            PrintUtil.printError(e);
        }
        try {
            if (set != null) set.close();
        } catch (SQLException e) {
            PrintUtil.printError(e);
        }

    }

    public static boolean isSqlWhere(String where) {
        return where.substring(0, 1).equals("(") && where.substring(where.length() - 1, where.length()).equals(")");
    }
}
